create      proc sp_check_lot_number_for_drop_shipment_final_inventory_pull
@lot_id int,
@lot_number varchar(20),
@partNumber varchar(50),
@location_id int,
@qtyPull int,
@csLineItem_id int,
@cust_shipping_status varchar(20),
@cslnt_is_drop_shipment int

As

declare @templot_id bigint
declare @processstep_id bigint
declare @templot_number varchar(20)
declare @vendor_name varchar(50)

begin
	select @processstep_id = ProcessStep_ID from Product where Part_Number =  @partNumber and p_marketing_Part_Number is not null
	if @lot_id = -1
	begin
		select @templot_id = Lot_Id from lot_table where Lot_Number = @lot_number and processstep_id = @processstep_id and location_id = @location_id
		if @templot_id is null
		begin
			declare @temp_date datetime
			set @temp_date = CONVERT(datetime, convert(varchar, current_timestamp, 101))
			select @vendor_name = L_Vendor_Name from Location where Location_id = @location_id
			exec sp_create_update_fg_lot @partNumber, @lot_number, @qtyPull, @qtyPull, @temp_date, @temp_date, 'Finished Goods', @vendor_name, null
			select @templot_id = Lot_Id, @templot_number = Lot_Number from lot_table where lot_number = @lot_number and processstep_id = @processstep_id and location_id = @location_id
			insert into customer_shipping_lot_number_table (CSLIT_ID, Lot_Id, CSLNT_Qty_Pull, CSLNT_Status, CSLNT_IS_Drop_Shipment) values 
			(@csLineItem_id, @templot_id, @qtyPull, @cust_shipping_status, @cslnt_is_drop_shipment)
			--print @templot_id
			--print @templot_number
		end
	
		else
		begin
			insert into customer_shipping_lot_number_table (CSLIT_ID, Lot_Id, CSLNT_Qty_Pull, CSLNT_Status, CSLNT_IS_Drop_Shipment) values 
			(@csLineItem_id, @templot_id, @qtyPull, @cust_shipping_status, @cslnt_is_drop_shipment)
		end

	end
	else
	begin
		insert into customer_shipping_lot_number_table (CSLIT_ID, Lot_Id, CSLNT_Qty_Pull, CSLNT_Status, CSLNT_IS_Drop_Shipment) values 
		(@csLineItem_id, @lot_id, @qtyPull, @cust_shipping_status, @cslnt_is_drop_shipment)
	end
end
